**This Stata .do file cleans the Indiana convictions data and creates letter-by-day panels for use in the Indiana analyses. 

insheet using "INconvictions.csv"
keep lastfirst
duplicates drop

forvalues i = 18993/20747{
g stub`i' = 0
}


reshape long stub, i(lastfirst) j(commitdt_m)
drop stub

save letterpanel.dta, replace

clear
set more off
insheet using "INconvictions.csv", names

g commitdt_m = date(commitdt, "MDY")
g commityr = year(commitdt_m)

g wmdt_m = date(wmdate, "MDY")
g wmyr = year(wmdt_m)


g any = 1
g drugs = 1 if category == "CONTRL SUB" 

g theft=0
replace theft=1 if aoff1=="THEFT,RECV STOL PROP" | aoff1=="THEFT,RECV,STOL PROP"

g drug_poss=0
replace drug_poss=1 if aoff1=="POSSESSION OF METH" | aoff1=="PRESCRIPTION FRAUD" | aoff1=="POSS COKE / NARCOTIC" | aoff1=="POSS CONTRLD SUBS TX" | aoff1=="POSS CONTROL SUBST" | aoff1=="POSS MARIJUANA/ HASH" | aoff1=="POSS METHAMP SUBS TX"
replace drug_poss=1 if aoff1=="POSS PRESC/ALEG DRUG" | aoff1=="POSS SMOKING INSTRUM" | aoff1=="POSS SYRINGE/NEEDLE" | aoff1=="POSS-COKE/NARCOTIC" | aoff1=="POSS-COKE/NARCOTICS" | aoff1=="POSS-CONTRLD SUBSTAN" | aoff1=="POSS-CONTRLD SUBSTNC"
replace drug_poss=1 if aoff1=="POSS-PARAPHERNALIA" | aoff1=="POSS. PARAPHERNALIA" | aoff1=="POSS. OF PRECURSORS" | aoff1=="POSS.MARIJ./HASH" | aoff1=="POSS/USE LEGEND DRUG" | aoff1=="ILL.POSS SCHEDULE IV" | aoff=="OP VEH W/SCH 1 W/DTH" | aoff1=="OPER VEH-CONTROL SUB"
g drug_deal=0
replace drug_deal=1 if aoff1=="CULT. OF MARIJUANA" | aoff1=="CULTIVATE MARIJUANA" | aoff1=="DEAL COUNTERFEIT SUB" | aoff1=="DEAL FAKE CNTL SUBST" | aoff1=="DEAL MARIJUANA, HASH" | aoff1=="DEAL MARIJUANA/ HASH"| aoff1=="DEAL SCHED 1,2,3 SUB" | aoff1=="DEAL/POSS LOOK-ALIKE"
replace drug_deal=1 if aoff1=="DEALING COKE/NARCOTC" | aoff1=="DEALING IN COCAINE" | aoff1=="DEALING IN METH" | aoff1=="DEALING LOOK-ALIKE" | aoff1=="DEALING SCHED 3 SUB" | aoff1=="DEALING SCHED 4 SUB" | aoff1=="DEALING-CNTERFIT SUB"
replace drug_deal=1 if aoff1=="DEALING-COKE/NARCOTC" | aoff1=="DEALING-MARIJ/HASH" | aoff1=="DEALING-REPRES SUBST" | aoff1=="DEALING-SCHED 1,2,3" | aoff1=="DEALING-SCHEDULE 4" | aoff1=="DEALING-SCHEDULE 5" | aoff1=="SALE OF LEGEND DRUG"

g alcohol=0
replace alcohol=1 if aoff1=="DRIVING INTOXICATED" | aoff1=="DRIVING-INTOX/DEATH" | aoff1=="DRIVING-INTOX/INJURY" | aoff1=="OP VEH .08 PRIOR CVT" | aoff1=="OP VEH .15 PRIOR CVT" | aoff1=="OP VEH INTOX ENDANGR"
replace alcohol=1 if aoff1=="OP VEH/INTOX C DEATH" | aoff1=="OPER VEH INTOX-DEATH" | aoff1=="OPER VEH INTX-INJURY" | aoff1=="OPER VEH-INTOXICATED" | aoff1=="OPER VEH-INTX/PR CVT" | aoff1=="PUBLIC INTOXICATION"


collapse (sum) any drugs theft alcohol, by(lastfirst commitdt_m)

merge 1:1 lastfirst commitdt_m using letterpanel.dta 

g commityr = year(commitdt_m)
drop if commitdt_m < 18993

foreach type in any drugs theft alcohol {
replace `type' = 0 if `type' == .
}

drop _merge
save IDOCpanel1.dta, replace


**FEMALE PANEL
clear
set more off
insheet using "INconvictions.csv", names

g commitdt_m = date(commitdt, "MDY")
g commityr = year(commitdt_m)

g wmdt_m = date(wmdate, "MDY")
g wmyr = year(wmdt_m)


g any = 1
g drugs = 1 if category == "CONTRL SUB" 

g theft=0
replace theft=1 if aoff1=="THEFT,RECV STOL PROP" | aoff1=="THEFT,RECV,STOL PROP"

g drug_poss=0
replace drug_poss=1 if aoff1=="POSSESSION OF METH" | aoff1=="PRESCRIPTION FRAUD" | aoff1=="POSS COKE / NARCOTIC" | aoff1=="POSS CONTRLD SUBS TX" | aoff1=="POSS CONTROL SUBST" | aoff1=="POSS MARIJUANA/ HASH" | aoff1=="POSS METHAMP SUBS TX"
replace drug_poss=1 if aoff1=="POSS PRESC/ALEG DRUG" | aoff1=="POSS SMOKING INSTRUM" | aoff1=="POSS SYRINGE/NEEDLE" | aoff1=="POSS-COKE/NARCOTIC" | aoff1=="POSS-COKE/NARCOTICS" | aoff1=="POSS-CONTRLD SUBSTAN" | aoff1=="POSS-CONTRLD SUBSTNC"
replace drug_poss=1 if aoff1=="POSS-PARAPHERNALIA" | aoff1=="POSS. PARAPHERNALIA" | aoff1=="POSS. OF PRECURSORS" | aoff1=="POSS.MARIJ./HASH" | aoff1=="POSS/USE LEGEND DRUG" | aoff1=="ILL.POSS SCHEDULE IV" | aoff=="OP VEH W/SCH 1 W/DTH" | aoff1=="OPER VEH-CONTROL SUB"
g drug_deal=0
replace drug_deal=1 if aoff1=="CULT. OF MARIJUANA" | aoff1=="CULTIVATE MARIJUANA" | aoff1=="DEAL COUNTERFEIT SUB" | aoff1=="DEAL FAKE CNTL SUBST" | aoff1=="DEAL MARIJUANA, HASH" | aoff1=="DEAL MARIJUANA/ HASH"| aoff1=="DEAL SCHED 1,2,3 SUB" | aoff1=="DEAL/POSS LOOK-ALIKE"
replace drug_deal=1 if aoff1=="DEALING COKE/NARCOTC" | aoff1=="DEALING IN COCAINE" | aoff1=="DEALING IN METH" | aoff1=="DEALING LOOK-ALIKE" | aoff1=="DEALING SCHED 3 SUB" | aoff1=="DEALING SCHED 4 SUB" | aoff1=="DEALING-CNTERFIT SUB"
replace drug_deal=1 if aoff1=="DEALING-COKE/NARCOTC" | aoff1=="DEALING-MARIJ/HASH" | aoff1=="DEALING-REPRES SUBST" | aoff1=="DEALING-SCHED 1,2,3" | aoff1=="DEALING-SCHEDULE 4" | aoff1=="DEALING-SCHEDULE 5" | aoff1=="SALE OF LEGEND DRUG"

g alcohol=0
replace alcohol=1 if aoff1=="DRIVING INTOXICATED" | aoff1=="DRIVING-INTOX/DEATH" | aoff1=="DRIVING-INTOX/INJURY" | aoff1=="OP VEH .08 PRIOR CVT" | aoff1=="OP VEH .15 PRIOR CVT" | aoff1=="OP VEH INTOX ENDANGR"
replace alcohol=1 if aoff1=="OP VEH/INTOX C DEATH" | aoff1=="OPER VEH INTOX-DEATH" | aoff1=="OPER VEH INTX-INJURY" | aoff1=="OPER VEH-INTOXICATED" | aoff1=="OPER VEH-INTX/PR CVT" | aoff1=="PUBLIC INTOXICATION"


keep if sex=="F"
collapse (sum) any drugs theft alcohol, by(lastfirst commitdt_m)

merge 1:1 lastfirst commitdt_m using ./Desktop/letterpanel.dta 

g commityr = year(commitdt_m)
drop if commitdt_m < 18993


foreach type in any drugs theft alcohol {
replace `type' = 0 if `type' == .
}

drop _merge
save IDOCpanel1_female.dta, replace


**HISPANIC PANEL
clear
set more off
insheet using "INconvictions.csv", names

g commitdt_m = date(commitdt, "MDY")
g commityr = year(commitdt_m)

g wmdt_m = date(wmdate, "MDY")
g wmyr = year(wmdt_m)


g any = 1
g drugs = 1 if category == "CONTRL SUB" 

g theft=0
replace theft=1 if aoff1=="THEFT,RECV STOL PROP" | aoff1=="THEFT,RECV,STOL PROP"

g drug_poss=0
replace drug_poss=1 if aoff1=="POSSESSION OF METH" | aoff1=="PRESCRIPTION FRAUD" | aoff1=="POSS COKE / NARCOTIC" | aoff1=="POSS CONTRLD SUBS TX" | aoff1=="POSS CONTROL SUBST" | aoff1=="POSS MARIJUANA/ HASH" | aoff1=="POSS METHAMP SUBS TX"
replace drug_poss=1 if aoff1=="POSS PRESC/ALEG DRUG" | aoff1=="POSS SMOKING INSTRUM" | aoff1=="POSS SYRINGE/NEEDLE" | aoff1=="POSS-COKE/NARCOTIC" | aoff1=="POSS-COKE/NARCOTICS" | aoff1=="POSS-CONTRLD SUBSTAN" | aoff1=="POSS-CONTRLD SUBSTNC"
replace drug_poss=1 if aoff1=="POSS-PARAPHERNALIA" | aoff1=="POSS. PARAPHERNALIA" | aoff1=="POSS. OF PRECURSORS" | aoff1=="POSS.MARIJ./HASH" | aoff1=="POSS/USE LEGEND DRUG" | aoff1=="ILL.POSS SCHEDULE IV" | aoff=="OP VEH W/SCH 1 W/DTH" | aoff1=="OPER VEH-CONTROL SUB"
g drug_deal=0
replace drug_deal=1 if aoff1=="CULT. OF MARIJUANA" | aoff1=="CULTIVATE MARIJUANA" | aoff1=="DEAL COUNTERFEIT SUB" | aoff1=="DEAL FAKE CNTL SUBST" | aoff1=="DEAL MARIJUANA, HASH" | aoff1=="DEAL MARIJUANA/ HASH"| aoff1=="DEAL SCHED 1,2,3 SUB" | aoff1=="DEAL/POSS LOOK-ALIKE"
replace drug_deal=1 if aoff1=="DEALING COKE/NARCOTC" | aoff1=="DEALING IN COCAINE" | aoff1=="DEALING IN METH" | aoff1=="DEALING LOOK-ALIKE" | aoff1=="DEALING SCHED 3 SUB" | aoff1=="DEALING SCHED 4 SUB" | aoff1=="DEALING-CNTERFIT SUB"
replace drug_deal=1 if aoff1=="DEALING-COKE/NARCOTC" | aoff1=="DEALING-MARIJ/HASH" | aoff1=="DEALING-REPRES SUBST" | aoff1=="DEALING-SCHED 1,2,3" | aoff1=="DEALING-SCHEDULE 4" | aoff1=="DEALING-SCHEDULE 5" | aoff1=="SALE OF LEGEND DRUG"

g alcohol=0
replace alcohol=1 if aoff1=="DRIVING INTOXICATED" | aoff1=="DRIVING-INTOX/DEATH" | aoff1=="DRIVING-INTOX/INJURY" | aoff1=="OP VEH .08 PRIOR CVT" | aoff1=="OP VEH .15 PRIOR CVT" | aoff1=="OP VEH INTOX ENDANGR"
replace alcohol=1 if aoff1=="OP VEH/INTOX C DEATH" | aoff1=="OPER VEH INTOX-DEATH" | aoff1=="OPER VEH INTX-INJURY" | aoff1=="OPER VEH-INTOXICATED" | aoff1=="OPER VEH-INTX/PR CVT" | aoff1=="PUBLIC INTOXICATION"


keep if race=="H"
collapse (sum) any drugs theft alcohol, by(lastfirst commitdt_m)

merge 1:1 lastfirst commitdt_m using letterpanel.dta 

g commityr = year(commitdt_m)
drop if commitdt_m < 18993


foreach type in any drugs theft alcohol {
replace `type' = 0 if `type' == .
}

drop _merge
save IDOCpanel1_hispanic.dta, replace


**BLACK PANEL
clear
set more off
insheet using "INconvictions.csv", names

g commitdt_m = date(commitdt, "MDY")
g commityr = year(commitdt_m)

g wmdt_m = date(wmdate, "MDY")
g wmyr = year(wmdt_m)


g any = 1
g drugs = 1 if category == "CONTRL SUB" 

g theft=0
replace theft=1 if aoff1=="THEFT,RECV STOL PROP" | aoff1=="THEFT,RECV,STOL PROP"

g drug_poss=0
replace drug_poss=1 if aoff1=="POSSESSION OF METH" | aoff1=="PRESCRIPTION FRAUD" | aoff1=="POSS COKE / NARCOTIC" | aoff1=="POSS CONTRLD SUBS TX" | aoff1=="POSS CONTROL SUBST" | aoff1=="POSS MARIJUANA/ HASH" | aoff1=="POSS METHAMP SUBS TX"
replace drug_poss=1 if aoff1=="POSS PRESC/ALEG DRUG" | aoff1=="POSS SMOKING INSTRUM" | aoff1=="POSS SYRINGE/NEEDLE" | aoff1=="POSS-COKE/NARCOTIC" | aoff1=="POSS-COKE/NARCOTICS" | aoff1=="POSS-CONTRLD SUBSTAN" | aoff1=="POSS-CONTRLD SUBSTNC"
replace drug_poss=1 if aoff1=="POSS-PARAPHERNALIA" | aoff1=="POSS. PARAPHERNALIA" | aoff1=="POSS. OF PRECURSORS" | aoff1=="POSS.MARIJ./HASH" | aoff1=="POSS/USE LEGEND DRUG" | aoff1=="ILL.POSS SCHEDULE IV" | aoff=="OP VEH W/SCH 1 W/DTH" | aoff1=="OPER VEH-CONTROL SUB"
g drug_deal=0
replace drug_deal=1 if aoff1=="CULT. OF MARIJUANA" | aoff1=="CULTIVATE MARIJUANA" | aoff1=="DEAL COUNTERFEIT SUB" | aoff1=="DEAL FAKE CNTL SUBST" | aoff1=="DEAL MARIJUANA, HASH" | aoff1=="DEAL MARIJUANA/ HASH"| aoff1=="DEAL SCHED 1,2,3 SUB" | aoff1=="DEAL/POSS LOOK-ALIKE"
replace drug_deal=1 if aoff1=="DEALING COKE/NARCOTC" | aoff1=="DEALING IN COCAINE" | aoff1=="DEALING IN METH" | aoff1=="DEALING LOOK-ALIKE" | aoff1=="DEALING SCHED 3 SUB" | aoff1=="DEALING SCHED 4 SUB" | aoff1=="DEALING-CNTERFIT SUB"
replace drug_deal=1 if aoff1=="DEALING-COKE/NARCOTC" | aoff1=="DEALING-MARIJ/HASH" | aoff1=="DEALING-REPRES SUBST" | aoff1=="DEALING-SCHED 1,2,3" | aoff1=="DEALING-SCHEDULE 4" | aoff1=="DEALING-SCHEDULE 5" | aoff1=="SALE OF LEGEND DRUG"

g alcohol=0
replace alcohol=1 if aoff1=="DRIVING INTOXICATED" | aoff1=="DRIVING-INTOX/DEATH" | aoff1=="DRIVING-INTOX/INJURY" | aoff1=="OP VEH .08 PRIOR CVT" | aoff1=="OP VEH .15 PRIOR CVT" | aoff1=="OP VEH INTOX ENDANGR"
replace alcohol=1 if aoff1=="OP VEH/INTOX C DEATH" | aoff1=="OPER VEH INTOX-DEATH" | aoff1=="OPER VEH INTX-INJURY" | aoff1=="OPER VEH-INTOXICATED" | aoff1=="OPER VEH-INTX/PR CVT" | aoff1=="PUBLIC INTOXICATION"



keep if race=="B"
collapse (sum) any drugs theft alcohol, by(lastfirst commitdt_m)

merge 1:1 lastfirst commitdt_m using ./Desktop/letterpanel.dta 

g commityr = year(commitdt_m)
drop if commitdt_m < 18993


foreach type in any drugs theft alcohol {
replace `type' = 0 if `type' == .
}

drop _merge
save IDOCpanel1_black.dta, replace


***WHITE PANEL
clear
set more off
insheet using "INconvictions.csv", names

g commitdt_m = date(commitdt, "MDY")
g commityr = year(commitdt_m)

g wmdt_m = date(wmdate, "MDY")
g wmyr = year(wmdt_m)


g any = 1
g drugs = 1 if category == "CONTRL SUB" 

g theft=0
replace theft=1 if aoff1=="THEFT,RECV STOL PROP" | aoff1=="THEFT,RECV,STOL PROP"


g drug_poss=0
replace drug_poss=1 if aoff1=="POSSESSION OF METH" | aoff1=="PRESCRIPTION FRAUD" | aoff1=="POSS COKE / NARCOTIC" | aoff1=="POSS CONTRLD SUBS TX" | aoff1=="POSS CONTROL SUBST" | aoff1=="POSS MARIJUANA/ HASH" | aoff1=="POSS METHAMP SUBS TX"
replace drug_poss=1 if aoff1=="POSS PRESC/ALEG DRUG" | aoff1=="POSS SMOKING INSTRUM" | aoff1=="POSS SYRINGE/NEEDLE" | aoff1=="POSS-COKE/NARCOTIC" | aoff1=="POSS-COKE/NARCOTICS" | aoff1=="POSS-CONTRLD SUBSTAN" | aoff1=="POSS-CONTRLD SUBSTNC"
replace drug_poss=1 if aoff1=="POSS-PARAPHERNALIA" | aoff1=="POSS. PARAPHERNALIA" | aoff1=="POSS. OF PRECURSORS" | aoff1=="POSS.MARIJ./HASH" | aoff1=="POSS/USE LEGEND DRUG" | aoff1=="ILL.POSS SCHEDULE IV" | aoff=="OP VEH W/SCH 1 W/DTH" | aoff1=="OPER VEH-CONTROL SUB"
g drug_deal=0
replace drug_deal=1 if aoff1=="CULT. OF MARIJUANA" | aoff1=="CULTIVATE MARIJUANA" | aoff1=="DEAL COUNTERFEIT SUB" | aoff1=="DEAL FAKE CNTL SUBST" | aoff1=="DEAL MARIJUANA, HASH" | aoff1=="DEAL MARIJUANA/ HASH"| aoff1=="DEAL SCHED 1,2,3 SUB" | aoff1=="DEAL/POSS LOOK-ALIKE"
replace drug_deal=1 if aoff1=="DEALING COKE/NARCOTC" | aoff1=="DEALING IN COCAINE" | aoff1=="DEALING IN METH" | aoff1=="DEALING LOOK-ALIKE" | aoff1=="DEALING SCHED 3 SUB" | aoff1=="DEALING SCHED 4 SUB" | aoff1=="DEALING-CNTERFIT SUB"
replace drug_deal=1 if aoff1=="DEALING-COKE/NARCOTC" | aoff1=="DEALING-MARIJ/HASH" | aoff1=="DEALING-REPRES SUBST" | aoff1=="DEALING-SCHED 1,2,3" | aoff1=="DEALING-SCHEDULE 4" | aoff1=="DEALING-SCHEDULE 5" | aoff1=="SALE OF LEGEND DRUG"

g alcohol=0
replace alcohol=1 if aoff1=="DRIVING INTOXICATED" | aoff1=="DRIVING-INTOX/DEATH" | aoff1=="DRIVING-INTOX/INJURY" | aoff1=="OP VEH .08 PRIOR CVT" | aoff1=="OP VEH .15 PRIOR CVT" | aoff1=="OP VEH INTOX ENDANGR"
replace alcohol=1 if aoff1=="OP VEH/INTOX C DEATH" | aoff1=="OPER VEH INTOX-DEATH" | aoff1=="OPER VEH INTX-INJURY" | aoff1=="OPER VEH-INTOXICATED" | aoff1=="OPER VEH-INTX/PR CVT" | aoff1=="PUBLIC INTOXICATION"



keep if race=="W"
collapse (sum) any drugs theft alcohol, by(lastfirst commitdt_m)

merge 1:1 lastfirst commitdt_m using ./Desktop/letterpanel.dta 

g commityr = year(commitdt_m)
drop if commitdt_m < 18993


foreach type in any drugs theft alcohol {
replace `type' = 0 if `type' == .
}

drop _merge
save IDOCpanel1_white.dta, replace

***AGES PANEL
clear
set more off
insheet using "INconvictions.csv", names

g commitdt_m = date(commitdt, "MDY")
g commityr = year(commitdt_m)

g wmdt_m = date(wmdate, "MDY")
g wmyr = year(wmdt_m)


g any = 1
g drugs = 1 if category == "CONTRL SUB" 


g theft=0
replace theft=1 if aoff1=="THEFT,RECV STOL PROP" | aoff1=="THEFT,RECV,STOL PROP"

g alcohol=0
replace alcohol=1 if aoff1=="DRIVING INTOXICATED" | aoff1=="DRIVING-INTOX/DEATH" | aoff1=="DRIVING-INTOX/INJURY" | aoff1=="OP VEH .08 PRIOR CVT" | aoff1=="OP VEH .15 PRIOR CVT" | aoff1=="OP VEH INTOX ENDANGR"
replace alcohol=1 if aoff1=="OP VEH/INTOX C DEATH" | aoff1=="OPER VEH INTOX-DEATH" | aoff1=="OPER VEH INTX-INJURY" | aoff1=="OPER VEH-INTOXICATED" | aoff1=="OPER VEH-INTX/PR CVT" | aoff1=="PUBLIC INTOXICATION"


*Note: agegroups: 1=<18, 2=18-24, 3=25-30, 4=30-35, 5=35-40, 6=>40
g yob=substr(dob,-4,.)
destring yob, replace
g age=commityr-yob
g agegroup=0
replace agegroup=1 if age <=18
replace agegroup=2 if age>18 & age<25
replace agegroup=3 if age>=25 & age<30
replace agegroup=4 if age>=30 & age<35
replace agegroup=5 if age>=35 & age<40
replace agegroup=6 if age>=40
collapse (sum) any drugs theft alcohol, by(lastfirst commitdt_m agegroup)

merge 1:1 lastfirst commitdt_m using ./Desktop/letterpanel.dta 

g commityr = year(commitdt_m)
drop if commitdt_m < 18993


foreach type in any drugs theft alcohol {
replace `type' = 0 if `type' == .
}

drop _merge
save IDOCpanel1_ages.dta, replace

